/**
 * Copyright (C) 2009-2013 FoundationDB, LLC
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package com.foundationdb.sql.aisddl;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;

import com.foundationdb.ais.model.ForeignKey;
import com.foundationdb.server.error.UnsupportedFunctionInIndexException;
import com.foundationdb.server.error.UnsupportedSQLException;

import org.junit.Test;

import com.foundationdb.ais.model.Column;
import com.foundationdb.ais.model.Index;
import com.foundationdb.ais.model.Table;
import com.foundationdb.ais.model.TableIndex;
import com.foundationdb.server.error.DuplicateSequenceNameException;
import com.foundationdb.server.error.NoSuchTableException;
import com.foundationdb.server.service.servicemanager.GuicedServiceManager;

import java.util.Map;

public class TableDDLIT extends AISDDLITBase {

    private static final String DROP_T1 = "DROP TABLE test.t1";
    private static final String DROP_T2 = "DROP TABLE test.t2";

    @Override
    protected GuicedServiceManager.BindingsConfigurationProvider serviceBindingsProvider() {
        return super.serviceBindingsProvider();
    }

    @Override
    protected Map<String, String> startupConfigProperties() {
        return uniqueStartupConfigProperties(TableDDLIT.class);
    }
    
    @Test (expected=NoSuchTableException.class)
    public void testDropFail() throws Exception {
        String sql = "DROP TABLE test.not_here";
        executeDDL(sql);
    }
    
    @Test
    public void testCreateSimple() throws Exception {
        String sqlCreate = "CREATE TABLE test.T1 (c1 integer not null primary key)";
        executeDDL(sqlCreate);
        
        assertNotNull (ais().getTable ("test", "t1"));
        
        executeDDL(DROP_T1);

        assertNull (ais().getTable("test", "t1"));
    }

    @Test
    public void testCreateIndexes() throws Exception {
        String sql = "CREATE TABLE test.t1 (c1 integer not null primary key, " + 
            "c2 integer not null, " +
            "constraint c2 unique (c2))";
        executeDDL(sql);

        Table table = ais().getTable("test", "t1");
        assertNotNull (table);
        
        assertNotNull (table.getPrimaryKey());
        assertEquals ("PRIMARY", table.getPrimaryKey().getIndex().getIndexName().getName());
        
        assertEquals (2, table.getIndexes().size());
        assertNotNull (table.getIndex("PRIMARY"));
        assertNotNull (table.getIndex("c2"));

        executeDDL(DROP_T1);
        
    }
    
    @Test
    public void testCreateJoin() throws Exception {
        int startNo = ais().getJoins().size();
        String sql1 = "CREATE TABLE test.t1 (c1 integer not null primary key)";
        String sql2 = "CREATE TABLE test.t2 (c1 integer not null primary key, " +
            "c2 integer not null, grouping foreign key (c2) references test.t1)";
        
        executeDDL(sql1);
        executeDDL(sql2);
        
        Table table = ais().getTable("test", "t2");
        assertNotNull (table);
        assertEquals (1, ais().getJoins().size() - startNo);
        assertNotNull (table.getParentJoin());
        executeDDL(DROP_T2);
        executeDDL(DROP_T1);
       
    }
    
    @Test
    public void testCreateAutoIncrement() throws Exception {
        String sql = "CREATE TABLE test.t1 (c1 integer not null primary key generated by default as identity " +
                    "(start with 1000, increment by 1))";
        executeDDL(sql);
        Table table = ais().getTable("test", "t1");
        
        assertNotNull(table);
        assertEquals("INT", table.getColumn(0).getTypeName());
        assertEquals(table.getPrimaryKey().getColumns().get(0), table.getColumn(0));
        assertEquals(1000L, table.getColumn(0).getIdentityGenerator().getStartsWith());
    }

    @Test
    public void testCreateInteger() throws Exception {
        
        String sql1 = "CREATE TABLE test.t1 (col1 INTEGER NOT NULL, col2 INTEGER, col3 smallint NOT NULL, "+ 
                    "col4 smallint, col5 bigint NOT NULL, col6 bigint, " +
                    "col7 INTEGER UNSIGNED, col8 smallint unsigned)";
        
        executeDDL(sql1);
        Table table = ais().getTable("test", "t1");
        
        assertNotNull (table);
        assertEquals ("INT", table.getColumn(0).getTypeName());
        assertFalse  (table.getColumn(0).getNullable());
        assertEquals ("INT", table.getColumn(1).getTypeName());
        assertTrue   (table.getColumn(1).getNullable());
        assertEquals ("SMALLINT", table.getColumn(2).getTypeName());
        assertFalse  (table.getColumn(2).getNullable());
        assertEquals ("SMALLINT", table.getColumn(3).getTypeName());
        assertTrue   (table.getColumn(3).getNullable());
        assertEquals ("BIGINT", table.getColumn(4).getTypeName());
        assertFalse  (table.getColumn(4).getNullable());
        assertEquals ("BIGINT", table.getColumn(5).getTypeName());
        assertTrue   (table.getColumn(5).getNullable());
        assertEquals ("INT UNSIGNED", table.getColumn(6).getTypeName());
        assertTrue   (table.getColumn(6).getNullable());
        assertEquals ("SMALLINT UNSIGNED", table.getColumn(7).getTypeName());
        assertTrue   (table.getColumn(7).getNullable());
        
        
    }
    
    @Test
    public void testCreateChar() throws Exception {
        String sql1 = "CREATE TABLE test.T1 (col10 CHAR(1) NOT NULL, col11 CHAR(1), " + 
            "col12 VARCHAR(1) NOT NULL, col13 VARCHAR(1), " +
            " col14 LONG VARCHAR NOT NULL, col15 LONG VARCHAR) ";
        executeDDL(sql1);
        Table table = ais().getTable("test", "t1");
        
        assertNotNull (table);
        assertEquals ("CHAR", table.getColumn(0).getTypeName());
        assertEquals (Long.valueOf(5), table.getColumn(0).getMaxStorageSize());
        assertEquals (Long.valueOf(1), table.getColumn(0).getTypeParameter1());
        assertEquals ("CHAR", table.getColumn(1).getTypeName());
        assertEquals (Long.valueOf(5), table.getColumn(1).getMaxStorageSize());
        assertEquals ("VARCHAR", table.getColumn(2).getTypeName());
        assertEquals (Long.valueOf(5), table.getColumn(2).getMaxStorageSize());
        assertEquals ("VARCHAR", table.getColumn(3).getTypeName());
        assertEquals (Long.valueOf(5), table.getColumn(3).getMaxStorageSize());
        assertEquals ("LONGTEXT", table.getColumn(4).getTypeName());
        assertFalse   (table.getColumn(4).getNullable());
        assertEquals (Long.valueOf(1047556), table.getColumn(4).getMaxStorageSize());
        assertEquals ("LONGTEXT", table.getColumn(5).getTypeName());
    }

    @Test
    public void testCreateTime() throws Exception {
        String sql1 = "CREATE TABLE test.t1 (col30 DATE NOT NULL, col31 DATE, "+ 
            "col32 TIME NOT NULL, col33 time, col34 timestamp NOT NULL, " +
        		"col35 timestamp)";
        executeDDL(sql1);
        Table table = ais().getTable("test", "t1");
        
        assertNotNull (table);
        assertEquals ("DATE", table.getColumn(0).getTypeName());
        assertFalse  (table.getColumn(0).getNullable());
        assertEquals ("DATE", table.getColumn(1).getTypeName());
        assertEquals ("TIME", table.getColumn(2).getTypeName());
        assertEquals ("TIME", table.getColumn(3).getTypeName());
        assertEquals ("DATETIME", table.getColumn(4).getTypeName());
        assertEquals ("DATETIME", table.getColumn(5).getTypeName());
    }
    
    @Test
    public void testCreateLOB() throws Exception {
        String sql1 = "CREATE TABLE test.t1 (col40 CLOB NOT NULL, col41 CLOB, col42 BLOB NOT NULL, col43 BLOB)";
        executeDDL(sql1);
        Table table = ais().getTable("test", "t1");
        
        assertNotNull (table);
        assertEquals ("LONGTEXT", table.getColumn(0).getTypeName());
        assertEquals ("LONGTEXT", table.getColumn(1).getTypeName());
        assertEquals ("BLOB", table.getColumn(2).getTypeName());
        assertEquals ("BLOB", table.getColumn(3).getTypeName());
    }
    
    @Test
    public void testCreateFloat() throws Exception {
        String sql1 = "CREATE TABLE test.t1 (col20 FLOAT NOT NULL,  col21 FLOAT, "+
            "col22 REAL NOT NULL, col23 REAL, col24 DOUBLE NOT NULL, col25 DOUBLE," +
            "col26 DOUBLE UNSIGNED, col27 REAL UNSIGNED, col28 NUMERIC)";
        executeDDL(sql1);
        Table table = ais().getTable("test", "t1");
        
        assertNotNull (table);
        assertEquals ("DOUBLE", table.getColumn(0).getTypeName());
        assertEquals ("DOUBLE", table.getColumn(1).getTypeName());
        assertEquals ("FLOAT", table.getColumn(2).getTypeName());
        assertEquals ("FLOAT", table.getColumn(3).getTypeName());
        assertEquals ("DOUBLE", table.getColumn(4).getTypeName());
        assertEquals ("DOUBLE", table.getColumn(5).getTypeName());
        assertEquals ("DOUBLE UNSIGNED", table.getColumn(6).getTypeName());
        assertEquals ("FLOAT UNSIGNED", table.getColumn(7).getTypeName());
        assertEquals ("DECIMAL", table.getColumn(8).getTypeName());
    }

    @Test
    public void testCreateDecimal () throws Exception {
        String sql1 = "CREATE TABLE test.t1 (col50 DECIMAL NOT NULL, col51 DECIMAL,"+
        "col52 DECIMAL (1) NOT NULL, "+
        "col54 DECIMAL (10) NOT NULL,  " +
        "col57 DECIMAL (1,1), col58 DECIMAL (10,1) NOT NULL, " +
        "col60 DECIMAL (10,10) NOT NULL, " +
        "col63 DECIMAL (30,10))";
        executeDDL(sql1);
        Table table = ais().getTable("test", "t1");
        
        assertNotNull (table);
        assertEquals ("DECIMAL", table.getColumn(0).getTypeName());
        assertEquals (Long.valueOf(5), table.getColumn(0).getTypeParameter1());
        assertEquals (Long.valueOf(0), table.getColumn(0).getTypeParameter2());
        assertEquals ("DECIMAL", table.getColumn(1).getTypeName());
        assertTrue   (table.getColumn(1).getNullable());
        assertEquals ("DECIMAL", table.getColumn(2).getTypeName());
        assertEquals (Long.valueOf(1), table.getColumn(2).getTypeParameter1());
        assertEquals (Long.valueOf(0), table.getColumn(2).getTypeParameter2());
        assertEquals ("DECIMAL", table.getColumn(3).getTypeName());
        assertEquals (Long.valueOf(10), table.getColumn(3).getTypeParameter1());
        assertEquals (Long.valueOf(0), table.getColumn(3).getTypeParameter2());

        assertEquals ("DECIMAL", table.getColumn(4).getTypeName());
        assertEquals (Long.valueOf(1), table.getColumn(4).getTypeParameter1());
        assertEquals (Long.valueOf(1), table.getColumn(4).getTypeParameter2());
        assertEquals ("DECIMAL", table.getColumn(5).getTypeName());
        assertEquals (Long.valueOf(10), table.getColumn(5).getTypeParameter1());
        assertEquals (Long.valueOf(1), table.getColumn(5).getTypeParameter2());
        assertEquals ("DECIMAL", table.getColumn(6).getTypeName());
        assertEquals (Long.valueOf(10), table.getColumn(6).getTypeParameter1());
        assertEquals (Long.valueOf(10), table.getColumn(6).getTypeParameter2());
        assertEquals ("DECIMAL", table.getColumn(7).getTypeName());
        assertEquals (Long.valueOf(30), table.getColumn(7).getTypeParameter1());
        assertEquals (Long.valueOf(10), table.getColumn(7).getTypeParameter2());
    }
    
    @Test
    public void createCustomers() throws Exception {
        String sql1 = "create table test.customers (" +
        "customer_id int not null primary key generated by default as identity," + 
        "customer_name varchar(255) not null,"+
        "primary_payment_code char(1) not null default 'C', " +
        "payment_status char(4) not null default 'ABCD', " + 
        "comment varchar(255))";

        String sql2 = "create table test.addresses (" +
        "customer_id int not null," +
        "state varchar(2) not null," +
        "zip_code varchar(5) not null," +
        "phone varchar (15)," + 
        "primary key (customer_id, zip_code)," +
        "grouping foreign key (customer_id) references customers)";

        executeDDL(sql1);
        Table table = ais().getTable("test", "customers");

        assertNotNull(table);
        assertEquals(5, table.getColumns().size());
        
        executeDDL(sql2);
        table = ais().getTable("test", "addresses");

        assertNotNull(table);
        assertEquals(4, table.getColumns().size());
        
    }

    @Test
    public void createIdentity () throws Exception {
        String sql = "CREATE TABLE test.t7 (c1 integer primary key generated by default as identity)";
        executeDDL(sql);
        Table table = ais().getTable("test", "t7");
        assertNotNull(table);
        Column column = table.getColumn(0);
        
        assertNotNull(column.getIdentityGenerator());
        assertEquals (1, column.getIdentityGenerator().getStartsWith());
        assertEquals (1, column.getIdentityGenerator().getIncrement());
        assertNotNull(column.getDefaultIdentity());
        assertTrue(column.getDefaultIdentity().booleanValue());
    }
    
    @Test
    public void createAlwaysIdentity() throws Exception {
        String sql = "CREATE TABLE test.t9 (c1 integer primary key generated ALWAYS as identity)";
        executeDDL(sql);
        Table table = ais().getTable("test", "t9");
        assertNotNull(table);
        Column column = table.getColumn(0);
        
        assertNotNull(column.getIdentityGenerator());
        assertEquals (1, column.getIdentityGenerator().getStartsWith());
        assertEquals (1, column.getIdentityGenerator().getIncrement());
        assertNotNull(column.getDefaultIdentity());
        assertFalse(column.getDefaultIdentity().booleanValue());
    }
    
    @Test
    public void createIdentityValues() throws Exception {
        String sql = "CREATE TABLE test.t10 (c1 integer primary key generated by default as identity (start with 11, increment by -1))";
        executeDDL(sql);
        Table table = ais().getTable("test", "t10");
        assertNotNull(table);
        Column column = table.getColumn(0);
        
        assertNotNull(column.getIdentityGenerator());
        assertEquals (11, column.getIdentityGenerator().getStartsWith());
        assertEquals (-1, column.getIdentityGenerator().getIncrement());
        assertNotNull(column.getDefaultIdentity());
        assertTrue(column.getDefaultIdentity().booleanValue());
        
    }
    
    @Test 
    public void dropTableWithIdentity() throws Exception {
        
        executeDDL("CREATE TABLE test.initiateSecManager (id INT PRIMARY KEY)");
        int startNo = ais().getSequences().size();
        
        String sql = "CREATE TABLE test.t11 (c1 integer primary key generated by default as identity)";
        executeDDL(sql);
        sql = "CREATE TABLE test.t12 (c1 integer primary key generated by default as identity)";
        executeDDL(sql);
        
        assertEquals (2, ais().getSequences().size() - startNo);
        
        sql = "DROP TABLE test.t11";
        executeDDL(sql);
        
        assertEquals (1, ais().getSequences().size() - startNo);
        
        sql = "DROP TABLE test.t12";
        executeDDL(sql);
        
        assertEquals (0, ais().getSequences().size() - startNo);
    }
    
    @Test
    public void createSerialTable() throws Exception {
        String sql = "CREATE TABLE test.t12 (c1 SERIAL PRIMARY KEY)";
        executeDDL(sql);
        Table table = ais().getTable("test", "t12");
        assertNotNull (table);
        Column column = table.getColumn(0);
        assertEquals ("INT", column.getTypeName());
        assertEquals (column.getNullable(), false);
        assertNotNull (column.getIdentityGenerator());
        assertEquals (1, column.getIdentityGenerator().getStartsWith());
        assertEquals (1, column.getIdentityGenerator().getIncrement());
        assertNotNull(column.getDefaultIdentity());
        assertTrue(column.getDefaultIdentity());
        // No column index auto created
        assertNull (table.getIndex("c1"));
        // Primary key is
        assertNotNull (table.getPrimaryKey());
        Index index = table.getPrimaryKey().getIndex();
        assertEquals (1, index.getKeyColumns().size());
    }
    
    
    @Test (expected=DuplicateSequenceNameException.class)
    public void createDoubleSerialTable() throws Exception {
        String sql = "CREATE TABLE test.t13 (c1 SERIAL PRIMARY KEY, c2 SERIAL)";
        executeDDL(sql);
    }
    
    @Test
    public void createIndexTable() throws Exception {
        String sql = "CREATE TABLE test.t14 (c1 INT NOT NULL PRIMARY KEY, c2 INT NOT NULL, INDEX (c2))";
        executeDDL(sql);
        Table table = ais().getTable("test", "t14");
        Index index = table.getIndex("c2");
        assertNotNull(index);
        assertFalse (index.isUnique());
        assertEquals(1, index.getKeyColumns().size());
        assertFalse(index.isGroupIndex());
        assertFalse(index.isSpatial());
    }
    
    @Test
    public void createNamedIndexTable() throws Exception {
        String sql = "CREATE TABLE test.t15 (c1 int NOT NULL, c2 INT NOT NULL, INDEX idx_c2 (c2))";
        executeDDL(sql);
        Table table = ais().getTable("test", "t15");
        Index index = table.getIndex("idx_c2");
        assertNotNull(index);
        assertFalse (index.isUnique());
        assertEquals(1, index.getKeyColumns().size());
        assertFalse(index.isGroupIndex());
        assertFalse(index.isSpatial());
    }

    @Test
    public void createSpatialIndexTable() throws Exception {
        String sql = "CREATE TABLE test.t16 (c1 decimal(11,7), c2 decimal(11,7), INDEX idx1 (geo_lat_lon(c1, c2)))";
        executeDDL (sql);
        Table table = ais().getTable("test", "t16");
        TableIndex index = table.getIndex("idx1");
        assertNotNull(index);
        assertFalse (index.isUnique());
        assertEquals(2, index.getKeyColumns().size());
        assertTrue (index.isSpatial());
    }
    
    @Test (expected=UnsupportedFunctionInIndexException.class)
    public void createIndexWithUnsupportedFunction() throws Exception {
        // z_order_lat_lon is no longer supported, use geo_lat_lon instead
        String sql = "CREATE TABLE test.t16 (c1 decimal(11,7), c2 decimal(11,7), INDEX idx1 (z_order_lat_lon(c1, c2)))";
        executeDDL(sql);
    }

    @Test
    public void testCreateNationalChar() throws Exception {
        String sql1 = "CREATE TABLE test.T1 (c1 NATIONAL CHAR(10), c2 NATIONAL CHARACTER VARYING(10), c3 LONG NVARCHAR) ";
        executeDDL(sql1);
        Table table = ais().getTable("test", "t1");
        
        assertNotNull (table);
        assertEquals ("CHAR", table.getColumn(0).getTypeName());
        assertEquals ("VARCHAR", table.getColumn(1).getTypeName());
        assertEquals ("LONGTEXT", table.getColumn(2).getTypeName());
    }

    @Test
    public void overlappingFKAndGFK() throws Exception {
        executeDDL("CREATE TABLE parent(pid INT NOT NULL PRIMARY KEY)");
        executeDDL("CREATE TABLE child (cid INT NOT NULL PRIMARY KEY, pid INT," +
                   "  FOREIGN KEY(pid) REFERENCES parent(pid)," +
                   "  GROUPING FOREIGN KEY(pid) REFERENCES parent(pid))");
        Table p = ais().getTable("test", "parent");
        Table c = ais().getTable("test", "child");
        assertNotNull(p);
        assertNotNull(c);
        assertEquals(p.getGroup(), c.getGroup());
        assertEquals(1, p.getReferencedForeignKeys().size());
        assertEquals(1, c.getReferencingForeignKeys().size());
    }

    @Test
    public void selfFK() throws Exception {
        executeDDL("CREATE TABLE t(id1 INT NOT NULL PRIMARY KEY, id2 INT, FOREIGN KEY(id2) REFERENCES t(id1))");
        Table t = ais().getTable("test", "t");
        assertNotNull(t);
        assertEquals(1, t.getForeignKeys().size());
        ForeignKey fk = t.getForeignKeys().iterator().next();
        assertEquals(t, fk.getReferencedTable());
        assertEquals(fk.getReferencedTable(), fk.getReferencingTable());
    }

    @Test(expected= UnsupportedSQLException.class)
    public void inlineGroupIndex() throws Exception {
        executeDDL("CREATE TABLE parent(pid INT PRIMARY KEY, x INT)");
        executeDDL("CREATE TABLE child(cid INT PRIMARY KEY, pid INT, y INT, "+
                   "  GROUPING FOREIGN KEY(pid) REFERENCES parent, "+
                   "  INDEX g_i (parent.x, child.y) USING LEFT JOIN)");
    }
}
